package com.isky.visual.db.service.impl;

import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.isky.visual.common.BasePageResult;
import com.isky.visual.constant.DbConstant;
import com.isky.visual.db.entity.*;
import com.isky.visual.db.enums.DataStoreDimensionEnums;
import com.isky.visual.db.factory.DbFactory;
import com.isky.visual.db.factory.IDbManager;
import com.isky.visual.db.mapper.DataStoreDimensionMapper;
import com.isky.visual.db.mapper.DatastoreMapper;
import com.isky.visual.db.mapper.MetaDataMapper;
import com.isky.visual.db.mapper.SourceMapper;
import com.isky.visual.db.service.ISourceService;
import com.isky.visual.db.vo.param.QueryTableDataParam;
import com.isky.visual.design.sqlparsefactory.ISqlParse;
import com.isky.visual.design.sqlparsefactory.SqlParseFactory;
import com.isky.visual.exception.GlobalException;
import com.isky.visual.interceptor.PlatformUserManager;
import com.isky.visual.result.CodeMsg;
import com.isky.visual.result.ResultVo;
import com.isky.visual.user.entity.User;
import com.isky.visual.util.DBUtil;
import com.isky.visual.util.LocalSqlMetaData;
import com.isky.visual.util.StringUtil;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.support.TransactionTemplate;

import javax.servlet.http.HttpServletResponse;
import java.sql.Connection;
import java.util.*;
import java.util.concurrent.atomic.AtomicReference;
import java.util.stream.Collectors;


/**
 * <p>
 * 数据库连接的配置表 服务实现类
 * </p>
 *
 * @author wudl
 * @since 2020-04-14
 */
@Service
public class SourceServiceImpl implements ISourceService {
    private static Logger log = LoggerFactory.getLogger(SourceServiceImpl.class);

    @Autowired
    SourceMapper sourceMapper;

    @Autowired
    MetaDataMapper metaDataMapper;

    @Autowired
    DatastoreMapper datastoreMapper;

    @Autowired
    TransactionTemplate transactionTemplate;

    @Autowired
    DataStoreDimensionMapper dataStoreDimensionMapper;

    @Value("${spring.datasource.url}")
    private String dsUrl;

    @Value("${spring.datasource.username}")
    private String dsUser;

    @Value("${spring.datasource.password}")
    private String dsPassWord;

    @Override
    public ResultVo<String> dbIsConnection(Source source) {
        IDbManager dbManager  = new DbFactory(source).getDbManager();
        dbManager.getConnection();
        return ResultVo.success(CodeMsg.DB_CONNECTIONS.getMsg());
    }

    @Override
    public ResultVo<Datastore> getSqlParse(DataStoreModel dataStoreModel) {
        List<Map<String, Object>> query = null;
        Objects.requireNonNull(dataStoreModel);

        String statName = dataStoreModel.getStatName();
        Integer sqltype = dataStoreModel.getSqltype();
        GenericSqlModel genericSqlModel = dataStoreModel.getJsons();
        Objects.requireNonNull(genericSqlModel);

        Long dbid = genericSqlModel.getDbid();
        Long dataStoreId = dataStoreModel.getId();
        if(dataStoreId != null){
            Datastore dt = datastoreMapper.selectById(dataStoreId);
            if(dt == null){
                return ResultVo.error(CodeMsg.DATA_STORE_NULL);
            }
            if(!Objects.equals(dt.getDbid(),dbid)){
                return ResultVo.error(CodeMsg.DB_CONN_ON_CHANGE);
            }
        }

        Source source = sourceMapper.selectById(dbid);
        if(null == source){
            return ResultVo.error(CodeMsg.DB_NOT_EXIST);
        }
        genericSqlModel.setDbName(source.getDbName());

        ISqlParse parser = SqlParseFactory.createParser(source.getDbType());
        String sql = parser.parse(genericSqlModel);

        try {
            Connection connection = new DbFactory(source).getDbManager().getConnection();
            if(!StringUtils.isBlank(sql)) {
                query = DBUtil.query(connection, sql);
            }
        } catch (Exception e) {
            log.error("数据源异常：{}",e);
            return ResultVo.error(CodeMsg.DB_GET_DATA_ERROR);
        }
        Long userId = PlatformUserManager.getUser().getId();
        AtomicReference<Datastore> datastoreResult = new AtomicReference<>();

        Boolean execute = transactionTemplate.execute((status) -> {
            Datastore datastore = new Datastore();
            datastore.setDbid(dbid);
            datastore.setUserId(userId);
            datastore.setStatName(statName);
            datastore.setSqls(sql);
            datastore.setSqltype(sqltype);
            String jsonString = JSON.toJSONString(genericSqlModel);
            datastore.setJsons(jsonString);

            if(dataStoreId != null){
                datastore.setId(dataStoreId);
                datastoreMapper.updateById(datastore);

                QueryWrapper<DataStoreDimension> deleteWrapper = new QueryWrapper<DataStoreDimension>();
                DataStoreDimension dataStoreDimension = new DataStoreDimension();
                dataStoreDimension.setDataStoreId(datastore.getId());
                deleteWrapper.setEntity(dataStoreDimension);
                dataStoreDimensionMapper.delete(deleteWrapper);
            }else{
                datastoreMapper.insert(datastore);
            }

            //保存子表信息
            int index = 0;
            List<String> splitColumn = StringUtil.getStrToSplit(sql);
            for (DataStoreDimensionEnums e : DataStoreDimensionEnums.values()) {
                if (index >= splitColumn.size()) {
                    break;
                }

                DataStoreDimension dataStoreDimension = new DataStoreDimension();
                dataStoreDimension.setColumnName(splitColumn.get(index));
                dataStoreDimension.setColumnOrder(index++);
                dataStoreDimension.setColumnType(e.getColumnType());
                dataStoreDimension.setRemark(e.getRemark());
                dataStoreDimension.setDataStoreId(datastore.getId());

                dataStoreDimensionMapper.insert(dataStoreDimension);
            }
            datastoreResult.set(datastore);

            return true;
        });
        return ResultVo.success(datastoreResult.get());

    }

    @Override
    public ResultVo<String> getShowSql(DataStoreModel dataStoreModel) {
        List<Map<String, Object>> query = null;
        Objects.requireNonNull(dataStoreModel);

        String statName = dataStoreModel.getStatName();
        Integer sqltype = dataStoreModel.getSqltype();
        GenericSqlModel genericSqlModel = dataStoreModel.getJsons();
        Objects.requireNonNull(genericSqlModel);

        Long dbid = genericSqlModel.getDbid();

        Source source = sourceMapper.selectById(dbid);
        if(null == source){
            return ResultVo.error(CodeMsg.DB_NOT_EXIST);
        }
        genericSqlModel.setDbName(source.getDbName());

        ISqlParse parser = SqlParseFactory.createParser(source.getDbType());
        String sql = parser.parse(genericSqlModel);
        return ResultVo.success(sql);
    }

    @Override
    public ResultVo<Datastore> GetCustomSqlPares(Map<String, String> mapdb) {
        List<Map<String, Object>> query = new ArrayList<>();
        Long dataStoreId =StringUtils.isBlank(mapdb.get("id"))? null: Long.valueOf(mapdb.get("id"));
        Long dbId = Long.valueOf(mapdb.get("dbid"));
        String sql = mapdb.get("sqls");
        String statName = mapdb.get("statName");
        int sqltype = Integer.valueOf(mapdb.get("sqltype"));
        checkSql(sql);
        Source source = sourceMapper.selectById(dbId);
        if(null == source){
            return ResultVo.error(CodeMsg.DB_NOT_EXIST);
        }
        IDbManager dbManager = new DbFactory(source).getDbManager();
        Connection connection = dbManager.getConnection();
        try {
            query = DBUtil.query(connection, sql);
        } catch (Exception e) {
            log.error("e+" + e.getMessage());
            return ResultVo.error(CodeMsg.DB_GET_DATA_ERROR);
        }

        AtomicReference<Datastore> datastoreResult = new AtomicReference<>();
        Boolean execute = transactionTemplate.execute((status) -> {
            User user = PlatformUserManager.getUser();
            Datastore datastore = new Datastore();
            datastore.setDbid(dbId);
            datastore.setUserId(user.getId());
            datastore.setStatName(statName);
            datastore.setSqls(sql);
            datastore.setSqltype(sqltype);

            if(dataStoreId != null){
                datastore.setId(dataStoreId);
                datastoreMapper.updateById(datastore);

                QueryWrapper<DataStoreDimension> deleteWrapper = new QueryWrapper<DataStoreDimension>();
                DataStoreDimension dataStoreDimension = new DataStoreDimension();
                dataStoreDimension.setDataStoreId(datastore.getId());
                deleteWrapper.setEntity(dataStoreDimension);
                dataStoreDimensionMapper.delete(deleteWrapper);
            }else{
                datastoreMapper.insert(datastore);
            }

            //保存子表信息
            int index = 0;
           String[] splitColumn = LocalSqlMetaData.getLocalSqlMetaData().split(",");
            for (DataStoreDimensionEnums e : DataStoreDimensionEnums.values()) {
                if (index >= splitColumn.length) {
                    break;
                }

                DataStoreDimension dataStoreDimension = new DataStoreDimension();
                dataStoreDimension.setColumnName(splitColumn[index]);
                dataStoreDimension.setColumnOrder(index++);
                dataStoreDimension.setColumnType(e.getColumnType());
                dataStoreDimension.setRemark(e.getRemark());
                dataStoreDimension.setDataStoreId(datastore.getId());

                dataStoreDimensionMapper.insert(dataStoreDimension);
            }
            datastoreResult.set(datastore);
            return true;
        });

        return ResultVo.success(datastoreResult.get());
    }

    private void checkSql(String sql){
        if(StringUtils.isBlank(sql)){
            throw new GlobalException(CodeMsg.DB_SELECT_ILLEGAL);
        }

        String newSql = sql.trim().toLowerCase();
        if(!newSql.startsWith("select")){
            throw new GlobalException(CodeMsg.DB_SELECT_ILLEGAL);
        }

        String[] split = newSql.split(";");
        if(split.length >1){
            throw new GlobalException(CodeMsg.DB_SELECT_ILLEGAL);
        }
    }

    @Override
    public ResultVo<List<Map<String, Object>>> GetCustomSqlShowData(Map<String, String> mapdb) {
        List<Map<String, Object>> query = new ArrayList<Map<String, Object>>();
        Long adbid = Long.valueOf((String) mapdb.get("dbid"));
        String sql = (String) mapdb.get("sqls");
        Source source = sourceMapper.selectById(adbid);
        if(null == source){
            return ResultVo.error(CodeMsg.DB_NOT_EXIST);
        }
        IDbManager dbManager = new DbFactory(source).getDbManager();
        Connection connection = dbManager.getConnection();
        try {
            query = DBUtil.query(connection, sql);
        } catch (Exception e) {
            log.error("e+" + e.getMessage());
            return ResultVo.error(CodeMsg.DB_GET_DATA_ERROR);
        }

        return ResultVo.success(query);
    }

    /**
     * 获取表数据量
     * @param source
     * @param dbManager
     * @param tableName
     * @return
     * @throws Exception
     */
    public Long getTableCount(Source source,IDbManager dbManager,String tableName) throws Exception {
        Long count = 0L;
        String singleTableCountSql = dbManager.getSingleTableCountSql(tableName);
        List<Map<String, Object>> countQuery = DBUtil.query(dbManager.getConnection(),singleTableCountSql);
        if(Objects.equals(source.getDbType(),DbConstant.SQL_SERVER)){
            List<Object> collect = countQuery.stream()
                    .filter(item -> item.get("name").equals(tableName))
                    .map(map -> map.get("COUNT"))
                    .collect(Collectors.toList());
            if(collect.size()>0){
                count = Long.valueOf(collect.get(0).toString());
            }
        }else{
            Map<String,Object> countMap = (Map<String,Object>)countQuery.get(0);
            count = Long.valueOf(countMap.get("COUNT").toString());
        }
        return count;
    }

    @Override
    public ResultVo<BasePageResult> getTableData(QueryTableDataParam queryTableDataParam) {
        List<Map<String, Object>> query = new ArrayList<>();
        BasePageResult pageResult = new BasePageResult();
        try {
            Long dbId = queryTableDataParam.getDbId();
            Integer rowNum = queryTableDataParam.getRowNum();
            String tableName = queryTableDataParam.getTableName();
            if (dbId == null || tableName == null) {
                return ResultVo.error(CodeMsg.QUERY_PARAMETER);
            }
            Source source = sourceMapper.selectById(dbId);
            if(null == source){
                return ResultVo.error(CodeMsg.DB_NOT_EXIST);
            }
            IDbManager dbManager = new DbFactory(source).getDbManager();
            // 获取表数据量
            Long tableCount = this.getTableCount(source,dbManager,tableName);
            pageResult.setTotal(tableCount);
            if(tableCount >0) {
                String singleTableDataSql = dbManager.getSingleTableDataSql(tableName,rowNum);
                query = DBUtil.query(dbManager.getConnection(), singleTableDataSql);
                pageResult.setRecords(query);
            }
        } catch (Exception e) {
            throw new GlobalException(CodeMsg.DB_GET_DATA_ERROR);
        }
        return ResultVo.success(pageResult);
    }

    @Override
    public ResultVo<Object> showDataResult(DataStoreModel dataStoreModel ) {
        GenericSqlModel genericSqlModel = dataStoreModel.getJsons();
        List<Map<String, Object>> query = new ArrayList<>();
        Long dbid = genericSqlModel.getDbid();
        //String statName = dataStoreModel.getStatName();
        Source sourceDb = sourceMapper.selectById(dbid);
        if(sourceDb == null ){
            throw new GlobalException(CodeMsg.DB_NOT_EXIST);
        }
        genericSqlModel.setDbName(sourceDb.getDbName());
        Source source = sourceMapper.selectById(dbid);
        if(null == source){
            return ResultVo.error(CodeMsg.DB_NOT_EXIST);
        }
        ISqlParse parser = SqlParseFactory.createParser(source.getDbType());
        String sql = parser.parse(genericSqlModel);

        try {
            IDbManager dbManager = new DbFactory(source).getDbManager();
            Connection connection = dbManager.getConnection();

            if(!StringUtils.isBlank(sql)) {
                query = DBUtil.query(connection, sql);
            }
        } catch (Exception e) {
            log.error("{}" ,e.getMessage());
            return ResultVo.error(CodeMsg.DB_GET_DATA_ERROR);
        }
        return ResultVo.success(query);
    }

    /**
     * 根据source 和表名  获取对应表字段信息
     * @param source
     * @param tableName
     * @return
     */
    List<Map<String, Object>> getColumnMateDateList(Source source,String tableName){
        List<Map<String, Object>> query = new ArrayList<>();
        try {
            IDbManager dbManager = new DbFactory(source).getDbManager();
            Connection connection = dbManager.getConnection();
            String sql = dbManager.getShowColumnSql(tableName,source);
            if(null!=connection){
                query = dbManager.getTableColumn(connection,sql);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new GlobalException(CodeMsg.DB_CONNECTION_ERROR);
        }
        return query;
    }


    @Override
    public ResultVo<Object> showColumn(Long dbId, String tableName) {
        List<Map<String, Object>> query = new ArrayList<Map<String, Object>>();
        if (StringUtils.isEmpty(tableName) || dbId == null) {
            return ResultVo.error(CodeMsg.QUERY_PARAMETER);
        }
        try {
            Source source = sourceMapper.selectById(dbId);
            if(null == source){
                return ResultVo.error(CodeMsg.DB_NOT_EXIST);
            }
            query = this.getColumnMateDateList(source,tableName);
        } catch (Exception e) {
            return ResultVo.error(CodeMsg.DB_QUERY_ERROE);
        }
        return ResultVo.success(query);
    }

    @Override
    public ResultVo<Object> showTable(Long dbId) {
        List<Map<String, Object>> query = new ArrayList<Map<String, Object>>();
        if (dbId == null) {
            return ResultVo.error(CodeMsg.QUERY_PARAMETER);
        }

        QueryWrapper<MetaData> wrapper = new QueryWrapper<MetaData>();
        wrapper.eq("database_id", dbId);
        List<MetaData> metaDataList = metaDataMapper.selectList(wrapper);

        return ResultVo.success(metaDataList);
    }

    /**
     * 根据链接和数据库信息获取 表元数据
     * @param dbConnection
     * @param source
     * @return
     */
    public List<Map<String, Object>> getTableMetaDataList(IDbManager dbManager, Connection dbConnection,Source source){
        List<Map<String, Object>> queryList = new ArrayList<>();
        // mysql
        if (Objects.equals(source.getDbType(), DbConstant.MYSQL)){
            queryList = DBUtil.queryMysqlTable(dbConnection, dbManager.getShowTableSql(), source.getId(), source.getDbName());
        // oracle  or sqlServer
        } else if(Objects.equals(source.getDbType(),DbConstant.ORACLE)||
                Objects.equals(source.getDbType(),DbConstant.SQL_SERVER)){
            queryList = DBUtil.queryOracleOrSqlServerTable(dbConnection,dbManager.getShowTableSql(),source.getId());
        } else if (Objects.equals(source.getDbType(), DbConstant.Hive)){
            queryList = DBUtil.queryPrestoTable(dbConnection,dbManager.getShowTableSql(),source.getId());
        }else{
            queryList = null;
        }
        return queryList;
    }

    @Override
    @Transactional(rollbackFor = GlobalException.class)
    public ResultVo<String> saveDbSource(Source source) {
        ResultVo<String> reusltVo = ResultVo.success();
        try {
            // 1. 获取连接
            IDbManager dbManager = new DbFactory(source).getDbManager();
            Connection dbConnection = dbManager.getConnection();
            if (null != dbConnection) {
                User user = PlatformUserManager.getUser();
                source.setUserId(user.getId());

                // 校验用户下数据库连接名是否已存在
                Map<String,Object> queryMap = new HashMap<>();
                queryMap.put("user_id",user.getId());
                queryMap.put("conn_name",source.getConnName());
                queryMap.put("valid",1);
                List<Source> sources = sourceMapper.selectByMap(queryMap);
                if(null!=sources && !sources.isEmpty()){
                    return  ResultVo.error(CodeMsg.DB_CONN_NAME_EXIT);
                }

                int insertCount = sourceMapper.insert(source);
            }
            // 2. 获取元数据
            List<Map<String, Object>> queryList = this.getTableMetaDataList(dbManager, dbConnection, source);

            //3.添加对应的元数据
            if(queryList.size()>0){
                int meta_data = DBUtil.insertAll(this.buildNewSource(), "meta_data", queryList);
            }
        } catch (Exception e) {
            e.printStackTrace();
            reusltVo = ResultVo.error(CodeMsg.DB_ADDDATA_ERROR);
        }
        return reusltVo;
    }

    public Source buildNewSource(){
        String url = dsUrl;
        String username = dsUser;
        String passWord = dsPassWord;
        Source dbSource = new Source();
        dbSource.setDbName(url.substring(url.lastIndexOf("/")+1, url.indexOf("?")));
        dbSource.setDbType(DbConstant.MYSQL);
        dbSource.setDbUrl(url.substring(url.indexOf("//")+2,url.lastIndexOf(":")));
        dbSource.setListenPort(Integer.valueOf(url.substring(url.lastIndexOf(":")+1, url.lastIndexOf("/"))));
        dbSource.setUserPwd(passWord);
        dbSource.setUserName(username);
        return dbSource;
    }


    @Override
    public ResultVo<String> updateDbSource(HttpServletResponse response, Source source) {

        if (source == null ||source.getId() == null
                || StringUtils.isEmpty(source.getConnName()) || StringUtils.isEmpty(source.getDbUrl())) {
            return ResultVo.error(CodeMsg.DB_CONNECTION_NULL.getMsg());
        }
        Source sourceOld = sourceMapper.selectById(source.getId());
        if(sourceOld==null){
            return  ResultVo.error(CodeMsg.DB_NOT_EXIST);
        }

        User user = PlatformUserManager.getUser();
        // 校验用户下数据库连接名是否已存在
        Map<String,Object> queryMap = new HashMap<>();
        queryMap.put("user_id",user.getId());
        queryMap.put("conn_name",source.getConnName());
        queryMap.put("valid",1);
        List<Source> sources = sourceMapper.selectByMap(queryMap);
        if(null!=sources && !sources.isEmpty()){
            for(Source s : sources){
                if(!Objects.equals(s.getId(),source.getId())){
                    return  ResultVo.error(CodeMsg.DB_CONN_NAME_EXIT);
                }
            } 
        }

        IDbManager dbManager = new DbFactory(source).getDbManager();
        Connection dbConnection = dbManager.getConnection();
        if (null == dbConnection) {
            return ResultVo.error(CodeMsg.DB_UPDATE_ERROR.getMsg());
        }
        sourceMapper. deleteMetaDataByDbId(source.getId());
        // 2. 获取元数据
        List<Map<String, Object>> queryList = this.getTableMetaDataList(dbManager, dbConnection, source);

        //3.添加对应的元数据
        if(queryList.size()>0){
            try {
                int meta_data = DBUtil.insertAll(this.buildNewSource(), "meta_data", queryList);
            } catch (Exception e) {
                return ResultVo.error(CodeMsg.DB_UPDATE_ERROR.getMsg());
            }
        }

        if(!Objects.equals(sourceOld.getDbName(),source.getDbName()) ||
                !Objects.equals(sourceOld.getDbUrl(),source.getDbUrl()) ||
                !Objects.equals(sourceOld.getListenPort(),source.getListenPort())){
            //这三个字段任意一个改了，就删除数据集结果
            datastoreMapper.logicDeleteByDbId(source.getId());
        }

        int ucount = sourceMapper.updateById(source);
        if (ucount > 0) {
            return ResultVo.success(CodeMsg.DB_UPDATE_SUCCESS.getMsg());
        }
        return ResultVo.error(CodeMsg.DB_UPDATE_ERROR.getMsg());
    }

    @Override
    public ResultVo<String> delDbSource(HttpServletResponse response,  Long dbId) {

        Boolean execute = transactionTemplate.execute(transactionStatus -> {

            sourceMapper.logicDelete(dbId);
            sourceMapper.deleteMetaDataByDbId(dbId);
            datastoreMapper.logicDeleteByDbId(dbId);
            return true;
        });

        return execute ? ResultVo.success(CodeMsg.DB_DELETE_SUCCESS.getMsg()) :
            ResultVo.error(CodeMsg.DB_DELETE_ERROR.getMsg());
    }



    @Override
    public ResultVo<List<Source>> getAllSource() {
//        List<Source> sourceless = sourceMapper.selectList(null);
        User user =PlatformUserManager.getUser();
        QueryWrapper queryWrapper = new QueryWrapper<Source>()
                .orderByDesc("update_time");
        Source source = new Source();
        source.setUserId(user.getId());
        source.setValid(true);
        queryWrapper.setEntity(source);

        List<Source> sourceless = sourceMapper.selectList(queryWrapper);
        return ResultVo.success(sourceless);
    }
}
